import 'package:flutter_1/utils/db/db.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

/// 数据库操作
class DatabaseHelper {
  static Database? _database;

  // 单例模式
  static Future<Database> getDatabase() async {
    if (_database != null) {
      return _database!;
    } else {
      _database = await _initDatabase();
      return _database!;
    }
  }

  // 初始化数据库
  static Future<Database> _initDatabase() async {
    String dbPath = await getDatabasesPath();
    String path = join(dbPath, 'xsj.db'); // 数据库文件路径

    return await openDatabase(
      path,
      version: dbVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  // 数据库创建时
  static Future<void> _onCreate(Database db, int version) async {
    for (var table in tables) {
      for (var tableInfo in table['info']) {
        String createTableSQL = tableInfo['sql'];
        await db.execute(createTableSQL);
      }
    }
  }

  // 数据库升级时
  static Future<void> _onUpgrade(
      Database db, int oldVersion, int newVersion) async {
    for (var dbVersionInfo in database) {
      if (dbVersionInfo['version'] == newVersion) {
        // 升级数据库时，根据需要执行SQL语句
        for (var tableInfo in dbVersionInfo['tables']) {
          String tableName = tableInfo['tablename'];
          int tableVersion = tableInfo['version'];
          if (tableVersion > oldVersion) {
            var table = tables
                .firstWhere((element) => element['tablename'] == tableName);
            var tableVersionInfo = table['info']
                .firstWhere((element) => element['version'] == tableVersion);
            String createTableSQL = tableVersionInfo['sql'];
            await db.execute(createTableSQL);
          }
        }
      }
    }
  }

  // 执行插入操作
  static Future<int> insert(String table, Map<String, dynamic> values) async {
    final db = await getDatabase();
    return await db.insert(table, values);
  }

  // 执行查询操作
  static Future<List<Map<String, dynamic>>> query(
    String table, {
    String? where,
    List<dynamic>? whereArgs,
  }) async {
    final db = await getDatabase();
    return await db.query(
      table,
      where: where,
      whereArgs: whereArgs,
    );
  }

  // 执行更新操作
  static Future<int> update(String table, Map<String, dynamic> values,
      String where, List<dynamic> whereArgs) async {
    final db = await getDatabase();
    return await db.update(table, values, where: where, whereArgs: whereArgs);
  }

  // 执行删除操作
  static Future<int> delete(
      String table, String where, List<dynamic> whereArgs) async {
    final db = await getDatabase();
    return await db.delete(table, where: where, whereArgs: whereArgs);
  }

  // 执行 JOIN 查询操作
  static Future<List<Map<String, dynamic>>> joinTables(
      String table1, String table2, String onCondition) async {
    final db = await getDatabase();

    // 使用 INNER JOIN 查询两个表
    String query = '''
      SELECT * FROM $table1
      INNER JOIN $table2 ON $onCondition
    ''';

    return await db.rawQuery(query);
  }
}
